#!/usr/bin/env python3
"""
市场数据数据库管理
管理市场基准数据和OCR历史数据
"""

import sqlite3
import os
import logging
from datetime import datetime
from typing import List, Dict, Optional, Any

class MarketDatabase:
    """市场数据数据库管理类"""
    
    def __init__(self, db_path: str = "database/market_data.db"):
        """初始化数据库连接"""
        self.db_path = db_path
        self.logger = logging.getLogger(__name__)
        
        # 确保数据库目录存在
        db_dir = os.path.dirname(db_path)
        if db_dir:  # 只有当目录不为空时才创建
            os.makedirs(db_dir, exist_ok=True)
        
        # 初始化数据库表
        self._init_database()
    
    def _init_database(self):
        """初始化数据库表结构"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 创建市场基准数据表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS market_baseline (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        stock_code TEXT UNIQUE NOT NULL,
                        stock_name TEXT NOT NULL,
                        position_ratio TEXT NOT NULL,
                        cost_price TEXT NOT NULL,
                        current_price TEXT NOT NULL,
                        profit_loss TEXT NOT NULL,
                        status TEXT NOT NULL,
                        sequence_num TEXT,
                        updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                ''')
                
                # 创建OCR历史数据表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS ocr_history (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        stock_code TEXT NOT NULL,
                        stock_name TEXT NOT NULL,
                        position_ratio TEXT NOT NULL,
                        cost_price TEXT NOT NULL,
                        current_price TEXT NOT NULL,
                        profit_loss TEXT NOT NULL,
                        status TEXT NOT NULL,
                        sequence_num TEXT,
                        scan_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        has_transaction_signal BOOLEAN DEFAULT FALSE
                    )
                ''')
                
                conn.commit()
                self.logger.info("✅ 数据库表初始化完成")
                
        except Exception as e:
            self.logger.error(f"❌ 数据库初始化失败: {e}")
            raise
    
    def get_baseline_data(self) -> List[Dict[str, Any]]:
        """获取市场基准数据"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT stock_code, stock_name, position_ratio, cost_price,
                           current_price, profit_loss, status, sequence_num
                    FROM market_baseline
                    ORDER BY sequence_num
                ''')
                
                results = cursor.fetchall()
                baseline_data = []
                
                for row in results:
                    baseline_data.append({
                        '代码': row[0],
                        '名称': row[1],
                        '仓位比例': row[2],
                        '成本价': row[3],
                        '最新价': row[4],
                        '盈亏比例': row[5],
                        '状态': row[6],
                        '序号': row[7] or str(len(baseline_data) + 1)
                    })
                
                self.logger.info(f"📊 从数据库获取基准数据: {len(baseline_data)} 条")
                
                # 🔍 详细显示基准数据内容（用于调试）
                if baseline_data:
                    self.logger.info(f"🔍 当前数据库基准数据详情:")
                    for item in baseline_data:
                        self.logger.info(f"   - {item['代码']}: {item['仓位比例']} ({item['名称']})")
                else:
                    self.logger.info(f"🔍 数据库中暂无基准数据（首次运行或已清空）")
                
                return baseline_data
                
        except Exception as e:
            self.logger.error(f"❌ 获取基准数据失败: {e}")
            return []
    
    def update_baseline_data(self, market_data: List[Dict[str, Any]], force_update: bool = False):
        """更新市场基准数据
        
        Args:
            market_data: 市场数据列表
            force_update: 是否强制更新（用于首次建立基准）
        """
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                updated_count = 0
                inserted_count = 0
                
                for data in market_data:
                    stock_code = data.get('代码', '')
                    stock_name = data.get('名称', '')
                    position_ratio = data.get('仓位比例', '')
                    cost_price = data.get('成本价', '')
                    current_price = data.get('最新价', '')
                    profit_loss = data.get('盈亏比例', '')
                    status = data.get('状态', '')
                    sequence_num = data.get('序号', '')
                    
                    if not stock_code:
                        continue
                    
                    # 检查是否已存在
                    cursor.execute('SELECT id FROM market_baseline WHERE stock_code = ?', (stock_code,))
                    existing = cursor.fetchone()
                    
                    if existing:
                        # 更新现有记录
                        cursor.execute('''
                            UPDATE market_baseline 
                            SET stock_name = ?, position_ratio = ?, cost_price = ?,
                                current_price = ?, profit_loss = ?, status = ?,
                                sequence_num = ?, updated_time = CURRENT_TIMESTAMP
                            WHERE stock_code = ?
                        ''', (stock_name, position_ratio, cost_price, current_price, 
                             profit_loss, status, sequence_num, stock_code))
                        updated_count += 1
                    else:
                        # 插入新记录
                        cursor.execute('''
                            INSERT INTO market_baseline 
                            (stock_code, stock_name, position_ratio, cost_price,
                             current_price, profit_loss, status, sequence_num)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                        ''', (stock_code, stock_name, position_ratio, cost_price,
                             current_price, profit_loss, status, sequence_num))
                        inserted_count += 1
                
                conn.commit()
                
                if force_update:
                    self.logger.info(f"🎯 首次建立数据库基准: 新增 {inserted_count} 条, 更新 {updated_count} 条")
                else:
                    self.logger.info(f"📊 更新数据库基准: 新增 {inserted_count} 条, 更新 {updated_count} 条")
                
                # 🔍 详细显示更新后的基准数据内容（用于调试）
                self.logger.info(f"🔍 更新后的数据库基准数据:")
                for data in market_data:
                    stock_code = data.get('代码', '')
                    position_ratio = data.get('仓位比例', '')
                    if stock_code:
                        self.logger.info(f"   - {stock_code}: {position_ratio}")
                
        except Exception as e:
            self.logger.error(f"❌ 更新基准数据失败: {e}")
            raise
    
    def update_single_baseline(self, stock_code: str, new_position: str):
        """更新单个股票的基准数据（交易确认后调用）"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                cursor.execute('''
                    UPDATE market_baseline 
                    SET position_ratio = ?, updated_time = CURRENT_TIMESTAMP
                    WHERE stock_code = ?
                ''', (new_position, stock_code))
                
                if cursor.rowcount > 0:
                    conn.commit()
                    self.logger.info(f"🔄 数据库基准已更新: {stock_code} → {new_position}")
                else:
                    self.logger.warning(f"⚠️ 未找到股票 {stock_code} 的基准数据")
                
        except Exception as e:
            self.logger.error(f"❌ 更新单个基准失败 {stock_code}: {e}")
    
    def save_ocr_history(self, market_data: List[Dict[str, Any]], has_transaction_signal: bool = False):
        """保存OCR历史数据"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                for data in market_data:
                    stock_code = data.get('代码', '')
                    stock_name = data.get('名称', '')
                    position_ratio = data.get('仓位比例', '')
                    cost_price = data.get('成本价', '')
                    current_price = data.get('最新价', '')
                    profit_loss = data.get('盈亏比例', '')
                    status = data.get('状态', '')
                    sequence_num = data.get('序号', '')
                    
                    if not stock_code:
                        continue
                    
                    cursor.execute('''
                        INSERT INTO ocr_history 
                        (stock_code, stock_name, position_ratio, cost_price,
                         current_price, profit_loss, status, sequence_num, has_transaction_signal)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ''', (stock_code, stock_name, position_ratio, cost_price,
                         current_price, profit_loss, status, sequence_num, has_transaction_signal))
                
                conn.commit()
                self.logger.info(f"💾 OCR历史已保存到数据库: {len(market_data)} 条数据, 交易信号: {has_transaction_signal}")
                
        except Exception as e:
            self.logger.error(f"❌ 保存OCR历史失败: {e}")
    
    def clear_baseline_data(self):
        """清空基准数据"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 🔍 清空前先查询记录数量
                cursor.execute('SELECT COUNT(*) FROM market_baseline')
                count_before = cursor.fetchone()[0]
                self.logger.info(f"🔍 清空基准数据前: 数据库中有 {count_before} 条基准记录")
                
                # 🔍 如果有数据，显示详细内容
                if count_before > 0:
                    cursor.execute('SELECT stock_code, position_ratio FROM market_baseline')
                    existing_data = cursor.fetchall()
                    for stock_code, position_ratio in existing_data:
                        self.logger.info(f"   - {stock_code}: {position_ratio}%")
                
                # 执行清空操作
                cursor.execute('DELETE FROM market_baseline')
                conn.commit()
                
                # 🔍 清空后再次确认
                cursor.execute('SELECT COUNT(*) FROM market_baseline')
                count_after = cursor.fetchone()[0]
                self.logger.info(f"🧹 基准数据清空完成: {count_before} → {count_after} 条记录")
                
        except Exception as e:
            self.logger.error(f"❌ 清空基准数据失败: {e}")
    
    def clear_ocr_history(self):
        """清空OCR历史数据"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 🔍 清空前先查询记录数量
                cursor.execute('SELECT COUNT(*) FROM ocr_history')
                count_before = cursor.fetchone()[0]
                self.logger.info(f"🔍 清空OCR历史前: 数据库中有 {count_before} 条历史记录")
                
                # 执行清空操作
                cursor.execute('DELETE FROM ocr_history')
                conn.commit()
                
                # 🔍 清空后再次确认
                cursor.execute('SELECT COUNT(*) FROM ocr_history')
                count_after = cursor.fetchone()[0]
                self.logger.info(f"🧹 OCR历史清空完成: {count_before} → {count_after} 条记录")
                
        except Exception as e:
            self.logger.error(f"❌ 清空OCR历史失败: {e}")
    
    def get_baseline_count(self) -> int:
        """获取基准数据数量"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('SELECT COUNT(*) FROM market_baseline')
                count = cursor.fetchone()[0]
                return count
                
        except Exception as e:
            self.logger.error(f"❌ 获取基准数据数量失败: {e}")
            return 0
    
    def get_stock_baseline(self, stock_code: str) -> Optional[Dict[str, Any]]:
        """获取指定股票的基准数据"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT stock_code, stock_name, position_ratio, cost_price,
                           current_price, profit_loss, status, sequence_num
                    FROM market_baseline
                    WHERE stock_code = ?
                ''', (stock_code,))
                
                row = cursor.fetchone()
                if row:
                    return {
                        '代码': row[0],
                        '名称': row[1],
                        '仓位比例': row[2],
                        '成本价': row[3],
                        '最新价': row[4],
                        '盈亏比例': row[5],
                        '状态': row[6],
                        '序号': row[7] or '1'
                    }
                return None
                
        except Exception as e:
            self.logger.error(f"❌ 获取股票基准数据失败 {stock_code}: {e}")
            return None
